﻿using System;
using System.Collections.Generic;
using System.Text;
using ZLDC.Common.Estate;
using System.Data;
using ZLDC.Topo.Model.Estate;

namespace ZLDC.Topo.BLL.Estate
{
                public class CustomerBLL
    {
                                                                                                                                                                                                                                                                                                                                                                                                                                        public static string CustomerSave(int Id, string customer, string customerTel, string customerClass, string occupation, string agent, string agentTel, string infoFrom, string buyingMotive, string remark, string dId, string dname, string aId, string area, string payType, string type, string Direct, int room, int room1, int hall, int restRoom, int balcony, int maxFloor, int minFloor, string houseStyle, string fitment, float minAcreage, float maxAcreage, float minPrice, float maxPrice, string priceUnit, int addUId, string AddUser, int AddDeptId, string AddDept, string owner, int ownerid, int ownerDeptId, string ownerDept, string ownerTel, int status, int isSelf, int dltFlag, string mark1, string mark2, string mark3, string modifyUser, int isValid, string tradetype, string usage, string estate, string estateid)
        {
            string outmsg = "";
            int newid = 0;
            using (_Database db = new _Database("estate"))
            {
                ZLDC.DAL.Estate.CustomerDAL.p_customer_save(Id, customer, customerTel, customerClass, occupation, agent, agentTel, infoFrom, buyingMotive, remark, dId, dname, aId, area, payType, type, Direct, room, room1, hall, restRoom, balcony, maxFloor, minFloor, houseStyle, fitment, minAcreage, maxAcreage, minPrice, maxPrice, priceUnit, addUId, AddUser, AddDeptId, AddDept, owner, ownerid, ownerDeptId, ownerDept, ownerTel, status, isSelf, dltFlag, mark1, mark2, mark3, modifyUser, isValid, tradetype, usage, estate, estateid, 0, out outmsg, out newid, db);
            }

            return outmsg;
        }

        public static string CustomerSave(int Id, string customer, string customerTel, string customerClass, string occupation, string agent, string agentTel, string infoFrom, string buyingMotive, string remark, string dId, string dname, string aId, string area, string payType, string type, string Direct, int room, int room1, int hall, int restRoom, int balcony, int maxFloor, int minFloor, string houseStyle, string fitment, float minAcreage, float maxAcreage, float minPrice, float maxPrice, string priceUnit, int addUId, string AddUser, int AddDeptId, string AddDept, string owner, int ownerid, int ownerDeptId, string ownerDept, string ownerTel, int status, int isSelf, int dltFlag, string mark1, string mark2, string mark3, string modifyUser, int isValid, string tradetype, string usage, string estate, string estateid, int ischange, ref int newid)
        {
            string outmsg = "";

            using (_Database db = new _Database("estate"))
            {
                ZLDC.DAL.Estate.CustomerDAL.p_customer_save(Id, customer, customerTel, customerClass, occupation, agent, agentTel, infoFrom, buyingMotive, remark, dId, dname, aId, area, payType, type, Direct, room, room1, hall, restRoom, balcony, maxFloor, minFloor, houseStyle, fitment, minAcreage, maxAcreage, minPrice, maxPrice, priceUnit, addUId, AddUser, AddDeptId, AddDept, owner, ownerid, ownerDeptId, ownerDept, ownerTel, status, isSelf, dltFlag, mark1, mark2, mark3, modifyUser, isValid, tradetype, usage, estate, estateid,ischange, out outmsg, out newid, db);
            }

            return outmsg;
        }

                                                                public static string p_customer_list(int page, int pagesize, string condition, string orderByName, string orderByType)
        {
            DataTable dt = null;
            int total = 0;                       int pageall = 0;         
            using (_Database db = new _Database("estate"))
            {
                dt = ZLDC.DAL.Estate.CustomerDAL.p_customer_list_table(page, pagesize, condition, orderByName, orderByType, ref total, db);
            }

            pageall = _Utility.GetPageAll(pagesize, total);      
            _Json json = new _Json(dt);
            return "{page:{currentpage:'" + page + "',pagesize:'" + pagesize + "',total:'" + total + "',pageall:'" + pageall + "'},record:" + json.ToJson() + "}";
        }

        public static string p_customer_list(int page, int pagesize, string condition)
        {
                        string orderByName = _Request.GetString("orderByName", "followUpDate");
            string orderByType = _Request.GetString("orderByType", "desc");

            return p_customer_list(page, pagesize, condition, orderByName, orderByType);
        }

                                                                public static string p_customer_list_action(int userid, int page, int pagesize, string condition)
        {
            DataTable dt = null;
            int total = 0;                       int pageall = 0;         
            using (_Database db = new _Database("estate"))
            {
                dt = ZLDC.DAL.Estate.CustomerDAL.p_customer_list_action_table(userid, page, pagesize, condition, ref total, db);
            }

            pageall = _Utility.GetPageAll(pagesize, total);      
                        string field = UserOptionBLL.p_user_option_detail(2, userid);

            _Json json = new _Json(dt);
            return "{" + field + ",page:{currentpage:'" + page + "',pagesize:'" + pagesize + "',total:'" + total + "',pageall:'" + pageall + "'},record:" + json.ToJson() + "}";
        }

                                                                public static string p_v_changeCustomerLog_list_table(int userid, int page, int pagesize, string condition, string orderByName, string orderByType)
        {
            DataTable dt = null;
            int total = 0;                       int pageall = 0;         
            using (_Database db = new _Database("estate"))
            {
                dt = ZLDC.DAL.Estate.CustomerDAL.p_v_changeCustomerLog_list_table(userid, page, pagesize, condition, orderByName, orderByType, ref total, db);
            }

            pageall = _Utility.GetPageAll(pagesize, total);      
                        string field = UserOptionBLL.p_user_option_detail(2, userid);

            _Json json = new _Json(dt);
            return "{" + field + ",page:{currentpage:'" + page + "',pagesize:'" + pagesize + "',total:'" + total + "',pageall:'" + pageall + "'},record:" + json.ToJson() + "}";
        }

                                                                        public static string p_MyCustomer_list(int userid, int page, int pagesize, string condition)
        {
            DataTable dt = null;
            int total = 0;
            int pageall = 0;
            using (_Database db = new _Database("estate"))
            {
                dt = ZLDC.DAL.Estate.CustomerDAL.p_customer_MyCustomerlist(userid, page, pagesize, condition, ref total, db);
            }
            pageall = _Utility.GetPageAll(pagesize, total);                              string field = UserOptionBLL.p_user_option_detail(2, userid);

            _Json json = new _Json(dt);
            return "{" + field + ",page:{currentpage:'" + page + "',pagesize:'" + pagesize + "',total:'" + total + "',pageall:'" + pageall + "'},record:" + json.ToJson() + "}";
        }

                                        public static CustomerInfo p_customer_detail(int id)
        {
            using (_Database db = new _Database("estate"))
            {
                return ZLDC.DAL.Estate.CustomerDAL.p_customer_detail(id, db);
            }
        }

                                                public static string p_customer_detail_table(int id)
        {
            DataTable dt = null;

            using (_Database db = new _Database("estate"))
            {
                dt = ZLDC.DAL.Estate.CustomerDAL.p_customer_detail_table(id, db);
            }

            _Json json = new _Json(dt, "");
            return "{record:" + json.ToJson() + "}";
        }

                                                public static string CustomerDel(int id)
        {
            string outmsg = "删除失败";
            int rows = 0;

            using (_Database db = new _Database("estate"))
            {
                rows = ZLDC.DAL.Estate.CustomerDAL.CustomerDel(id, db);
                if (rows > 0)
                    outmsg = "删除成功";
            }

            return outmsg;
        }

                                                public static string CustomerChange(int id, int isself)
        {
            string outmsg = "";
          
            int ownerid = _Convert.ToInt(_Utility.GetInfoBySession().m_sUserID, 0);
            string owner = _Utility.GetInfoBySession().m_sUserName;
            int ownerDeptid = _Convert.ToInt(_Utility.GetInfoBySession().m_sDeptID, 0);
            string ownerDept = _Utility.GetInfoBySession().m_sDeptName;
            string station = _Utility.GetInfoBySession().m_sStation;                        int mark1 = 1;  
            if (_Utility.GetWebConfigSetting("SalesManagerStation").Contains(station))
                mark1 = 0;

            using (_Database db = new _Database("estate"))
            {                                              
                ZLDC.DAL.Estate.CustomerDAL.p_customer_change(isself, mark1, id, ownerid, owner, ownerDeptid, ownerDept, out outmsg, db);
            }

            return outmsg;
        }

                                                        public static int CustomerChangeSelf(int id, int isself)
        {
            using (_Database db = new _Database("estate"))
            {
                return ZLDC.DAL.Estate.CustomerDAL.CustomerChangeSelf(id, isself, db);
            }
        }

                                                                                                                                public static string p_Customerfollow_save(int Id, int CustomerId, int fDeptId, string fDept, int fUserId, string fUser, string fType, string fContent, int dltFlag, int fHousingId)
        {
            string outmsg = "";
            int newid = 0;

            using (_Database db = new _Database("estate"))
            {
                ZLDC.DAL.Estate.CustomerDAL.p_Customerfollow_save(Id, CustomerId, fDeptId, fDept, fUserId, fUser, fType, fContent, dltFlag, fHousingId,out outmsg, out newid, db);
            }

            return outmsg;
        }

        public static int p_Customerfollow_save(int Id, int CustomerId, int fDeptId, string fDept, int fUserId, string fUser, string fType, string fContent, int dltFlag, int fHousingId, string fHousingIdList,string CustomerIdList, string paperNo)
        {
            string   outmsg = "";
            int  newid = 0;

            using (_Database db = new _Database("estate"))
            {
                ZLDC.DAL.Estate.CustomerDAL.p_Customerfollow_save(Id, CustomerId, fDeptId, fDept, fUserId, fUser, fType, fContent, dltFlag, fHousingId,fHousingIdList,CustomerIdList,paperNo,out outmsg, out newid, db);
            }
            return newid;
           
        }

                                public static string p_customerAlert_save(int Id, int customerId, string AlertType, string AlertContent, int AlertScope, DateTime AlertDate, int AddDeptId, string AddDept, int AddUserId, string AddUser, int dFlag, int dDeptId, string dDept, int dUserId, string dUser, int dltFlag)
        {
            string outmsg = "";
            int newid = 0;
            using (_Database db = new _Database("estate"))
            {
                ZLDC.DAL.Estate.CustomerDAL.p_customerAlert_save(Id, customerId, AlertType, AlertContent, AlertScope, AlertDate, AddDeptId, AddDept, AddUserId, AddUser, dltFlag, dDeptId, dDept, dUserId, dUser, dltFlag, out outmsg, out newid, db);
            }
            return outmsg;
        }

                                                                public static string p_customerFollow_List(int page, int pagesize, int customerId, int deptid)
        {
            DataTable dt = null;
            int total = 0;                       int pageall = 0;         
            using (_Database db = new _Database("estate"))
            {
                if (pagesize == 6)
                    dt = ZLDC.DAL.Estate.CustomerDAL.p_Customerfollow_list_table(page, pagesize, customerId, deptid, ref total, db);
                else
                    dt = ZLDC.DAL.Estate.CustomerDAL.p_Customerfollow_list_top50_table(page, pagesize, customerId, ref total, db);
            }

            pageall = _Utility.GetPageAll(pagesize, total);      
            _Json json = new _Json(dt, "yyyy-MM-dd HH:mm:ss");
            return "{page:{currentpage:'" + page + "',pagesize:'" + pagesize + "',total:'" + total + "',pageall:'" + pageall + "'},record:" + json.ToJson() + "}";


        }
                                                           public static string p_Customerfollow_delete(int Id) 
        {
            string outmsg = "";
            using (_Database db = new _Database("estate")) 
            {
                ZLDC.DAL.Estate.CustomerDAL.p_Customerfollow_delete(Id, out outmsg, db);
            }
            return outmsg;
        }

                                                                public static string p_customerAlert_list(int page, int pagesize, int customerId,int userid)
        {
            DataTable dt = null;
            int total = 0;
            int pageall = 0;
            using (_Database db = new _Database("estate"))
            {
                dt = ZLDC.DAL.Estate.CustomerDAL.p_customerAlert_list_table(page, pagesize, customerId, userid, ref total, db);
            }
            pageall = _Utility.GetPageAll(pagesize, total);
            _Json json = new _Json(dt, "yyyy-MM-dd HH:mm:ss");
            return "{page:{currentpage:'" + page + "',pagesize:'" + pagesize + "',total:'" + total + "',pageall:'" + pageall + "'},record:" + json.ToJson() + "}";

        }

                public static int p_CustomerAlert_update(int Id, int dFlag, int dDeptId, string dDept, int dUserId, string dUser)
        {
            int retval = 0;
            using (_Database db = new _Database("estate"))
            {
                ZLDC.DAL.Estate.CustomerDAL.p_CustomerAlert_update(Id, dFlag, dDeptId, dDept, dUserId, dUser, out retval, db);
            }
            return retval;

        }

                public static string p_CustomerfollowReport_list_table(int page, int pagesize, string condition)
        {
            DataTable dt = null;
            int total = 0;
            int pageall = 0;
            using (_Database db = new _Database("estate"))
            {
                dt = ZLDC.DAL.Estate.CustomerDAL.p_CustomerfollowReport_list_table(page, pagesize, condition, ref total, db);

            }
            pageall = _Utility.GetPageAll(pagesize, total);                  _Json json = new _Json(dt, "yyyy-MM-dd HH:mm:ss");
            return "{page:{currentpage:'" + page + "',pagesize:'" + pagesize + "',total:'" + total + "',pageall:'" + pageall + "'},record:" + json.ToJson() + "}";

        }

                                                                public static string lp_salecount(string condition, string ftype, string rdvalue)
        {
            DataTable dt = null;
            int total = 0;
            int pageall = 0;
            using (_Database db = new _Database("estate"))
            {
                if (ftype == "带看租房")
                {
                    dt = ZLDC.DAL.Estate.CustomerDAL.lp_salecount(lp_leasepricesql(condition), db); 
                }
                else
                {
                    dt = ZLDC.DAL.Estate.CustomerDAL.lp_salecount(lp_salecountsql(condition), db);
                }
            }
           
            _Json json = new _Json(dt, "yyyy-MM-dd HH:mm:ss");
            return "{page:{total:'" + total + "',pageall:'" + pageall + "',ftype:'" + ftype + "',rdvalue:'" + rdvalue + "'},record:" + json.ToJson() + "}";

        }

                                                                public static string lp_Area(string condition, string ftype, string rdvalue)
        {
            DataTable dt = null;
            int total = 0;
            int pageall = 0;
            using (_Database db = new _Database("estate"))
            {
                dt = ZLDC.DAL.Estate.CustomerDAL.lp_salecount(lp_Areasql(condition), db);

            }

            _Json json = new _Json(dt, "yyyy-MM-dd HH:mm:ss");
            return "{page:{total:'" + total + "',pageall:'" + pageall + "',ftype:'" + ftype + "',rdvalue:'" + rdvalue + "'},record:" + json.ToJson() + "}";

        }

                                                public static string lp_leasepricesql(string condition)
        {
            string sql = "SELECT     '1万及以上' as 价格,COUNT(t_Customerfollow.id) as count " +
                       " FROM         dbo.t_customer INNER JOIN " +
                       " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                       " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                       " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND  dbo.t_housing.leaseprice>=10000 " +
                       " and " + condition + " ";
            sql += " union all    ";
            sql += " SELECT     '8000至10000' as 价格,COUNT(t_Customerfollow.id) as count " +
                        " FROM         dbo.t_customer INNER JOIN " +
                        " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                        " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                        " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND  dbo.t_housing.leaseprice<=10000 and dbo.t_housing.leaseprice>=8000 " +
                        "  and " + condition + " ";
            sql += " union all    ";
            sql += " SELECT     '5000至8000' as 价格,COUNT(t_Customerfollow.id) as count " +
                        " FROM         dbo.t_customer INNER JOIN " +
                        " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                        " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                        " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND   dbo.t_housing.leaseprice<=8000 and dbo.t_housing.leaseprice>=5000 " +
                        " and " + condition + " ";
            sql += " union all    ";
            sql += " SELECT     '3000至5000' as 价格,COUNT(t_Customerfollow.id) as count " +
                        " FROM         dbo.t_customer INNER JOIN " +
                        " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                        " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                        " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND   dbo.t_housing.leaseprice<=5000 and dbo.t_housing.leaseprice>=3000 " +
                        " and " + condition + " ";
            sql += " union all    ";
            sql += " SELECT     '1000至3000' as 价格,COUNT(t_Customerfollow.id) as count " +
                        " FROM         dbo.t_customer INNER JOIN " +
                        " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                        " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                        " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND   dbo.t_housing.leaseprice<=3000 and dbo.t_housing.leaseprice>=1000 " +
                        " and " + condition + " ";
            sql += " union all    ";
            sql += " SELECT     '1000及以下' as 价格,COUNT(t_Customerfollow.id) as count " +
                        " FROM         dbo.t_customer INNER JOIN " +
                        " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                        " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                        " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND   dbo.t_housing.leaseprice<=1000 " +
                        " and " + condition + " ";
            return sql;
        }


                                                public static string lp_salecountsql(string condition)
        {
            string sql = "SELECT     '1000万及以上' as 价格,COUNT(t_Customerfollow.id) as count " +
                       " FROM         dbo.t_customer INNER JOIN " +
                       " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                       " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                       " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND  dbo.t_housing.totalprice>=1000 " +
                       " and " + condition + " ";
            sql += " union all    ";
            sql += " SELECT     '800万至1000万' as 价格,COUNT(t_Customerfollow.id) as count " +
                        " FROM         dbo.t_customer INNER JOIN " +
                        " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                        " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                        " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND  dbo.t_housing.totalprice<=1000 and dbo.t_housing.totalprice>=800 " +
                        "  and " + condition + " ";
            sql += " union all    ";
            sql += " SELECT     '500万至800万' as 价格,COUNT(t_Customerfollow.id) as count " +
                        " FROM         dbo.t_customer INNER JOIN " +
                        " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                        " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                        " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND   dbo.t_housing.totalprice<=800 and dbo.t_housing.totalprice>=500 " +
                        " and " + condition + " ";
            sql += " union all    ";
            sql += " SELECT     '300万至500万' as 价格,COUNT(t_Customerfollow.id) as count " +
                        " FROM         dbo.t_customer INNER JOIN " +
                        " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                        " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                        " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND   dbo.t_housing.totalprice<=500 and dbo.t_housing.totalprice>=300 " +
                        " and " + condition + " ";
            sql += " union all    ";
            sql += " SELECT     '100万至300万' as 价格,COUNT(t_Customerfollow.id) as count " +
                        " FROM         dbo.t_customer INNER JOIN " +
                        " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                        " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                        " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND   dbo.t_housing.totalprice<=300 and dbo.t_housing.totalprice>=100 " +
                        " and " + condition + " ";
            sql += " union all    ";
            sql += " SELECT     '100万及以下' as 价格,COUNT(t_Customerfollow.id) as count " +
                        " FROM         dbo.t_customer INNER JOIN " +
                        " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                        " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                        " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND   dbo.t_housing.totalprice<=100 " +
                        " and " + condition + " ";
            return sql;
        }

                                                public static string lp_Areasql(string condition)
        {
                        string sql = "SELECT     '144平以上' as 面积,COUNT(t_Customerfollow.id) as count " +
                       " FROM         dbo.t_customer INNER JOIN " +
                       " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                       " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                       " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND  dbo.t_housing.acreage>144 " +
                       " and " + condition + " ";
                                                                                                                                                                                                                                                                        sql += " union all    ";
            sql += " SELECT     '90平至144平' as 面积,COUNT(t_Customerfollow.id) as count " +
                        " FROM         dbo.t_customer INNER JOIN " +
                        " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                        " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                        " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND   dbo.t_housing.acreage<=144 and dbo.t_housing.acreage>90 " +
                        " and " + condition + " ";
            sql += " union all    ";
            sql += " SELECT     '90平及以下' as 面积,COUNT(t_Customerfollow.id) as count " +
                        " FROM         dbo.t_customer INNER JOIN " +
                        " dbo.t_Customerfollow ON dbo.t_customer.Id = dbo.t_Customerfollow.CustomerId LEFT OUTER JOIN " +
                        " dbo.t_housing ON dbo.t_Customerfollow.fHousingId = dbo.t_housing.Id " +
                        " WHERE     (dbo.t_Customerfollow.dltFlag = 0) AND   dbo.t_housing.acreage<=90 " +
                        " and " + condition + " ";
            return sql;
        }
                public static string lp_CustomerfollowReport_list_table(int page, int pagesize, string condition, string condition_1, string ftype, string rdvalue, string groupbyfiled, string showfiled, string orderbyfiled, string depttype)
        {
            DataTable dt = null;
            int total = 0;
            int pageall = 0;
            using (_Database db = new _Database("estate"))
            {
                dt = ZLDC.DAL.Estate.CustomerDAL.lp_CustomerfollowReport_list_table(page, pagesize, condition, condition_1, ref total, db, groupbyfiled, showfiled, orderbyfiled, depttype);

            }
            pageall = _Utility.GetPageAll(pagesize, total);                  _Json json = new _Json(dt, "yyyy-MM-dd HH:mm:ss");
            return "{page:{currentpage:'" + page + "',pagesize:'" + pagesize + "',total:'" + total + "',pageall:'" + pageall + "',ftype:'" + ftype + "',rdvalue:'" + rdvalue + "'},record:" + json.ToJson() + "}";

        }

                                                                public static string p_customerAlertReport_list_table(int page, int pagesize, string condition)
        {
            DataTable dt = null;
            int total = 0;
            int pageall = 0;
            using (_Database db = new _Database("estate"))
            {
                dt = ZLDC.DAL.Estate.CustomerDAL.p_customerAlertReport_list_table(page, pagesize, condition, ref total, db);
            }
            pageall = _Utility.GetPageAll(pagesize, total);                  _Json json = new _Json(dt, 1);
            return "{page:{currentpage:'" + page + "',pagesize:'" + pagesize + "',total:'" + total + "',pageall:'" + pageall + "'},record:" + json.ToJson() + "}";

        }

                                                                public static string p_Customer_Follow_Report_list_table(int page, int pagesize, string condition)
        {
            DataTable dt = null;
            int total = 0;
            int pageall = 0;
            using (_Database db = new _Database("estate"))
            {
                dt = ZLDC.DAL.Estate.CustomerDAL.p_Customer_Follow_Report_list_table(page, pagesize, condition, ref total, db);
            }
            pageall = _Utility.GetPageAll(pagesize, total);                  _Json json = new _Json(dt, 1);
            return "{page:{currentpage:'" + page + "',pagesize:'" + pagesize + "',total:'" + total + "',pageall:'" + pageall + "'},record:" + json.ToJson() + "}";

        }

                public static DataSet SelectCustomersById(int id, string FiledList)
        {
            DataSet ds = null;
            using (_Database db = new _Database("estate"))
            {
                ds = ZLDC.DAL.Estate.CustomerDAL.SelectCustomersById(id, FiledList, db);
            }
            return ds;
        }

        public static string SelectMyCustomer(int size, string condition)
        {
            string fields = "top " + size + " id, customer,customerTel,customerClass,tradeType, dname, area, estates, room1, room,mark2";
                        string orderby = " ";
            DataTable dt = new DataTable();
            using (_Database db = new _Database("estate"))
            {
                DataSet ds = ZLDC.DAL.Estate.CustomerDAL.SelectCustomers(condition, fields, orderby, db);
                dt = ds.Tables[0];
            }
            _Json json = new _Json(dt);
            return "{ record:" + json.ToJson() + "}";
        }

                public static int UpdateStatus(string id, int deptid_from, string deptname_from, int userid_from, string username_from, string flag, int userid_to, int deptid_to, string deptname_to, string username_to)
        {
            int rows = 0;
            using (_Database db = new _Database("estate"))
            {
                rows = ZLDC.DAL.Estate.CustomerDAL.UpdateStatus(id, deptid_from, deptname_from, userid_from, username_from, flag, userid_to, deptid_to, deptname_to, username_to, db);
            }
            return rows;
        }

                                                        public static string OpenStatus(int id, int typeid)
        {
            string outmsg = "转客失败!";

            using (_Database db = new _Database("estate"))
            {
                int rows = ZLDC.DAL.Estate.CustomerDAL.OpenStatus(id, typeid, db);

                if (rows > 0)
                    outmsg = "转客成功!";
            }

            return outmsg;
        }

                public static string DeleteCustomer(int id)
        {
            string outmsg = "删除失败";
            int newid = 0;

            using (_Database db = new _Database("estate"))
            {
                newid = ZLDC.DAL.Estate.CustomerDAL.DeleteCustomer(id, db);
            }

            if (newid > 0)
                outmsg = "删除成功";

            return outmsg;
        }

        public static string DeleteWeekDaysCustomer()
        {
            string outmsg = "删除失败";
            int newid = 0;

            using (_Database db = new _Database("estate"))
            {
                newid = ZLDC.DAL.Estate.CustomerDAL.DeleteWeekDaysCustomer(db);
            }

            if (newid > 0)
                outmsg = "删除成功";

            return outmsg;
        }

        public static string UpdateDltFlag(int id)
        {
            string outmsg = "恢复失败";
            int newid = 0;

            using (_Database db = new _Database("estate"))
            {
                newid = ZLDC.DAL.Estate.CustomerDAL.UpdateDltFlag(id, db);
            }

            if (newid > 0)
                outmsg = "恢复成功";

            return outmsg;
        }

                public static DataSet SelectHousingOrCustomerCount(string id)
        {
            using (_Database db = new _Database("estate"))
            {
                return ZLDC.DAL.Estate.CustomerDAL.SelectHousingOrCustomerCount(id, db);
            }
        }

                public static string GetCustTelList(string CustTel)
        {
            DataTable dt = null;
            using (_Database db = new _Database("estate"))
            {
                dt = ZLDC.DAL.Estate.CustomerDAL.GetCustTelList(CustTel, db);
            }
            _Json json = new _Json(dt);

            return "{ record:" + json.ToJson() + "}";
        }
                public static  DataSet SelectIsSelfById(int id) 
        {
            
            DataSet ds = null;
            using (_Database db = new _Database("estate")) 
            {
                 ds = ZLDC.DAL.Estate.CustomerDAL.SelectIsSelfById(id,db);
            }

           

            return ds;
        }

                public static string CustomerToDatum(int id, int status)
        { 
            using (_Database db=new _Database ("estate")){

                return ZLDC.DAL.Estate.CustomerDAL.CustomerToDatum(id, status, db);
            }
        }

                public static string ManagerChangeIsSelf(int id, int ownerid, string owner, int ownerdeptid, string ownerdept)
        {
            using (_Database db = new _Database("estate")) 
            {
                return ZLDC.DAL.Estate.CustomerDAL.ManagerChangeIsSelf(id, ownerid, owner, ownerdeptid, ownerdept, db);
            }
        }
                public static string CustomerToTransactions(int id)
        {
            using (_Database db = new _Database("estate")) 
            {
                return ZLDC.DAL.Estate.CustomerDAL.CustomerToTransactions(id, db);
            }
        }
                public static string DeleteCustomerAlert(int id)
        {
            using (_Database db = new _Database("estate")) 
            {
                return ZLDC.DAL.Estate.CustomerDAL.DeleteCustomerAlert(id, db);
            }
        }
                public static void UpdateTranRemark(int id, string tranRemark)
        {
            using (_Database db = new _Database("estate")) 
            {
                ZLDC.DAL.Estate.CustomerDAL.UpdateTranRemark(id,tranRemark,db);
            }
        }

        public static int ChangeCustomerCount(int deptid, string Tel)
        {
            using (_Database db = new _Database("estate"))
            {
                return ZLDC.DAL.Estate.CustomerDAL.ChangeCustomerCount(deptid, Tel, db);
            }
        }
        public static int ChangeCustomerCountByUserId(int UserId, string Tel)
        {
            using (_Database db = new _Database("estate")) 
            {
                return ZLDC.DAL.Estate.CustomerDAL.ChangeCustomerCountByUserId(UserId, Tel, db);
            }
        }

        public static int CustomerFollowByTypeOrDate(int id) 
        {
            using (_Database db = new _Database("estate")) 
            {
                return ZLDC.DAL.Estate.CustomerDAL.CustomerFollowByTypeOrDate(id,db);
            }
        }


                                                public static string ShowOtherCustomer(int id)
        {
            DataTable dt = null;

            using (_Database db = new _Database("estate"))
            {
                dt = ZLDC.DAL.Estate.CustomerDAL.ShowOtherCustomer(id, db);
            }

            _Json json = new _Json(dt);
            return "{record:" + json.ToJson() + "}";
        }
                                        public static void UpdateLookCustomerCount(int id)
        {
            using (_Database db = new _Database("estate")) 
            {
                ZLDC.DAL.Estate.CustomerDAL.UpdateLookCustomerCount(id,db);
            }
        }

        public static void p_Customerfollow_housingMore_save(int customerFollowId, int housingId, string housingName)
        {   
            string outmsg="";
            int newid=0;
            using (_Database db = new _Database("estate")) 
            {
                ZLDC.DAL.Estate.CustomerDAL.p_Customerfollow_housingMore_save(customerFollowId, housingId, housingName, out outmsg, out newid, db);
            }

        }
        public static void p_Customerfollow_customerMore_save(int customerFollowId,int  teamworkUserId, string teamworkUserName,int teamworkDeptId) 
        {
            string outmsg = "";
            int newId = 0;
            using (_Database db = new _Database("estate")) 
            {
                ZLDC.DAL.Estate.CustomerDAL.p_Customerfollow_customerMore_save(customerFollowId, teamworkUserId, teamworkUserName,teamworkDeptId,out outmsg,out newId,db);
            }
        }
                                                public static void UpdateChangeCustomerClasss(string customerClass, int Id)
        {
            using (_Database db = new _Database("estate")) 
            {
                ZLDC.DAL.Estate.CustomerDAL.UpdateChangeCustomerClasss(customerClass, Id, db);
            }

        }
    }
}
